import send_msg from '@feidao-factory/send-msg';
import config from '@feidao-factory/send-msg/nodejs/config';
import check from './_check_undefined';

const spaceid = config.spaceid;

class Service {
	private _fields = [] as string[][];
	private _relations = [] as Array<Array<(string | string[][])>>;
	private _criteria = {};
	private _groupby = [] as Array<[string, string]>;
	private _orderby = [] as Array<[string, string, 1 | -1]>;

	private page_size = 0;
	private page_no = 1;
	private _distinct = 0;

	constructor(private action_id: string, private session_id: string, private systemid: string) {
	}

	/**
	 * add_field 添加查询字段
	 * @param tablename 表明
	 * @param fieldname 字段名
	 * @param alias 别名
	 *
	 * @example .add_field('表名', '字段名', '别名')
	 */
	public add_field(tablename: string, fieldname: string, alias: string) {
		this._fields.push([tablename, fieldname, alias, '']);
		return this;
	}

	/**
	 * add_fun 使用sql函数
	 * @param tablename 表名
	 * @param fieldname 字段名
	 * @param alias 别名
	 * @param funname 函数名 'count' | 'max' | 'min' | 'avg' | 'sum'
	 *
	 * @example .add_fun('表名', '字段名', '别名', '函数名')
	 * @tip 别名不要和函数名一样
	 */
	public add_fun(tablename: string, fieldname: string, alias: string, funname: 'count' | 'max' | 'min' | 'avg' | 'sum') {
		this._fields.push([tablename, fieldname, alias, funname]);
		return this;
	}

	/**
	 * inner_join 内连接
	 * @param table1 左表名
	 * @param table2 右表名
	 * @param fields 两表连接用的字段名
	 *
	 * @example .inner_join('tableName1', 'tableName2', ['city', 'city'], ['age', 'age'])
	 */
	public inner_join(table1: string, table2: string, ...fields: Array<[string, string]>) {
		return this.join('inner', table1, table2, ...fields);
	}

	/**
	 * outer_left_join 左外连接
	 * @param table1 左表名
	 * @param table2 右表名
	 * @param fields 两表连接用的字段名
	 *
	 * @example .outer_left_join('tableName1', 'tableName2', ['city', 'city'], ['age', 'age'])
	 */
	public outer_left_join(table1: string, table2: string, ...fields: Array<[string, string]>) {
		return this.join('leftouter', table1, table2, ...fields);
	}

	/**
	 * outer_right_join 右外连接
	 * @param table1 左表名
	 * @param table2 右表名
	 * @param fields 两表连接用的字段名
	 *
	 * @example .outer_right_join('tableName1', 'tableName2', ['city', 'city'], ['age', 'age'])
	 */
	public outer_right_join(table1: string, table2: string, ...fields: Array<[string, string]>) {
		return this.join('rightouter', table1, table2, ...fields);
	}

	/**
	 * where_eq 单表相等查询
	 * @param tablename 表名
	 * @param fieldname 字段名
	 * @param value 值 string | number | boolean | null
	 *
	 * @example .where_eq('tableName', 'no', '001')
	 */
	public where_eq(tablename: string, fieldname: string, value: string | number | boolean | null) {
		return this.where(tablename, fieldname, value);
	}

	/**
	 * where_not_null 单表不等于查询
	 * @param tablename 表名
	 * @param fieldname 字段名*
	 *
	 * @example .where_not_null('tableName', 'no')
	 */
	public where_not_null(tablename: string, fieldname: string) {
		return this.where(tablename, fieldname, {
			$ne: null
		});
	}

	// 建议使用inner_join
	public where_eq_tb(tablename1: string, fieldname1: string, tablename2: string, fieldname2: string) {
		return this.where(tablename1, fieldname1, {
			$field: `${tablename2}.${fieldname2}`
		});
	}

	/**
	 * where_gt 单表大于
	 * @param tablename 表名
	 * @param fieldname 字段名
	 * @param value 值
	 *
	 * @example .where_lte('tableName', 'num', 20)
	 */
	public where_gt(tablename: string, fieldname: string, value: string | number | boolean | null) {
		return this.where(tablename, fieldname, {
			$gt: value
		});
	}

	/**
	 * where_gt_tb A表某字段值大于B表某字段值
	 * @param tablename1 表名一
	 * @param fieldname1 表一字段名
	 * @param tablename2 表名二
	 * @param fieldname2 表二字段名
	 *
	 * @example .where_gt_tb('tableName1', 'num', 'tableName2', 'num')
	 */
	public where_gt_tb(tablename1: string, fieldname1: string, tablename2: string, fieldname2: string) {
		return this.where(tablename1, fieldname1, {
			$gt: {
				$field: `${tablename2}.${fieldname2}`
			}
		});
	}

	/**
	 * where_gt 单表大于等于
	 * @param tablename 表名
	 * @param fieldname 字段名
	 * @param value 值
	 *
	 * @example .where_gte('tableName', 'num', 20)
	 */
	public where_gte(tablename: string, fieldname: string, value: string | number | boolean | null) {
		return this.where(tablename, fieldname, {
			$gte: value
		});
	}

	/**
	 * where_gte_tb A表某字段值大于等于B表某字段值
	 * @param tablename1 表名一
	 * @param fieldname1 表一字段名
	 * @param tablename2 表名二
	 * @param fieldname2 表二字段名
	 *
	 * @example .where_gte_tb('tableName1', 'num', 'tableName2', 'num')
	 */
	public where_gte_tb(tablename1: string, fieldname1: string, tablename2: string, fieldname2: string) {
		return this.where(tablename1, fieldname1, {
			$gte: {
				$field: `${tablename2}.${fieldname2}`
			}
		});
	}

	/**
	 * where_gt 单表小于
	 * @param tablename 表名
	 * @param fieldname 字段名
	 * @param value 值
	 *
	 * @example .where_lt('tableName', 'num', 20)
	 */
	public where_lt(tablename: string, fieldname: string, value: string | number | boolean | null) {
		return this.where(tablename, fieldname, {
			$lt: value
		});
	}

	/**
	 * where_lt_tb A表某字段值小于B表某字段值
	 * @param tablename1 表名一
	 * @param fieldname1 表一字段名
	 * @param tablename2 表名二
	 * @param fieldname2 表二字段名
	 *
	 * @example .where_lt_tb('tableName1', 'num', 'tableName2', 'num')
	 */
	public where_lt_tb(tablename1: string, fieldname1: string, tablename2: string, fieldname2: string) {
		return this.where(tablename1, fieldname1, {
			$lt: {
				$field: `${tablename2}.${fieldname2}`
			}
		});
	}

	/**
	 * where_gt 单表小于等于
	 * @param tablename 表名
	 * @param fieldname 字段名
	 * @param value 值
	 *
	 * @example .where_lte('tableName', 'num', 20)
	 */
	public where_lte(tablename: string, fieldname: string, value: string | number | boolean | null) {
		return this.where(tablename, fieldname, {
			$lte: value
		});
	}

	/**
	 * where_lte_tb A表某字段值小于等于B表某字段值
	 * @param tablename1 表名一
	 * @param fieldname1 表一字段名
	 * @param tablename2 表名二
	 * @param fieldname2 表二字段名
	 *
	 * @example .where_lte_tb('tableName1', 'num', 'tableName2', 'num')
	 */
	public where_lte_tb(tablename1: string, fieldname1: string, tablename2: string, fieldname2: string) {
		return this.where(tablename1, fieldname1, {
			$lte: {
				$field: `${tablename2}.${fieldname2}`
			}
		});
	}

	/**
	 * where_neq 单表不相等查询
	 * @param tablename 表名
	 * @param fieldname 字段名
	 * @param value 值 string | number | boolean | null
	 *
	 * @example .where_neq('tableName', 'no', '001')
	 */
	public where_neq(tablename: string, fieldname: string, value: string | number | boolean | null) {
		return this.where(tablename, fieldname, {
			$ne: value
		});
	}

	/**
	 * where_neq_tb 两表某字段不相等
	 * @param tablename1 表名一
	 * @param fieldname1 表一字段名
	 * @param tablename2 表名二
	 * @param fieldname2 表二字段名
	 */
	public where_neq_tb(tablename1: string, fieldname1: string, tablename2: string, fieldname2: string) {
		return this.where(tablename1, fieldname1, {
			$ne: {
				$field: `${tablename2}.${fieldname2}`
			}
		});
	}
	/**
	 * like
	 * @param tablename 表名
	 * @param fieldname 字段名
	 * @param value 要匹配的值，%表示0到多个任意值_表示单个任意字符
	 */
	public where_like(tablename: string, fieldname: string, value: string) {
		return this.where(tablename, fieldname, {
			$like: value
		});
	}
	/**
	 * 正则
	 * @param tablename 表名
	 * @param fieldname 字段名
	 * @param reg 正则表达式
	 */
	public where_reg(tablename: string, fieldname: string, reg: RegExp) {
		return this.where(tablename, fieldname, {
			$options: reg.flags,
			$regex: `^${reg.source}$`
		});
	}
	// todo 数据库服务暂时不支持
	// public where_unlike(tablename: string, fieldname: string, reg: RegExp) {
	// 	return this.where(tablename, fieldname, `^^${(reg.source)}$`);
	// }

	/**
	 * where_in in操作符
	 * @param tablename 表名
	 * @param fieldname 字段名
	 * @param value 值, 数组类型
	 *
	 * @example .where_in('tableName', 'no', ['001', '002'])
	 */
	public where_in(tablename: string, fieldname: string, value: Array<string | number | boolean>) {
		return this.where(tablename, fieldname, {
			$in: value
		});
	}

	/**
	 * where_not_in   no in操作符
	 * @param tablename 表名
	 * @param fieldname 字段名
	 * @param value 值, 数组类型
	 *
	 * @example .where_not_in('tableName', 'no', ['001', '002'])
	 */
	public where_not_in(tablename: string, fieldname: string, value: Array<string | number | boolean>) {
		return this.where(tablename, fieldname, {
			$nin: value
		});
	}

	/**
	 * groupby 分组
	 * @param tablename 表名
	 * @param fieldname 字段名
	 */
	public groupby(tablename: string, fieldname: string) {
		this._groupby.push([tablename, fieldname]);
		return this;
	}

	/**
	 * orderby 正序
	 * @param alias 别名
	 */
	public orderby(alias: string) {
		this._orderby.push(['', alias, 1]);
		return this;
	}

	/**
	 * orderby_desc 逆序
	 * @param alias 别名
	 */
	public orderby_desc(alias: string) {
		this._orderby.push(['', alias, -1]);
		return this;
	}

	/**
	 * page 分页
	 * @param page_size 每页总条数
	 * @param page_no 页码
	 */
	public page(page_size: number, page_no: number) {
		this.page_no = page_no;
		this.page_size = page_size;
		return this;
	}

	/**
	 * 去重
	 */
	public distinct() {
		this._distinct = 1;
		return this;
	}

	public async exec() {
		const action_id = this.action_id;
		if (this._fields.length === 0) {
			throw new Error('Please add 1 field at least');
		}
		if (this._relations.length === 0) {
			// 单表查询
			this._relations.push([this._fields[0][0], '', '', []]);
		}
		const params = {
			condition: {
				criteria: this._criteria,
				distinct: this._distinct,
				fields: this._fields,
				groupby: this._groupby,
				orderby: this._orderby,
				page: {
					no: this.page_no,
					size: this.page_size
				},
				relations: this._relations
			},
			spaceid,
			systemid: this.systemid
		};
		const data = await send_msg<{ result: Array<{ [field: string]: string | boolean | number }> }>(action_id, this.session_id, 'dbacc.complexfind.intranet', params);
		this.init();
		return data.result;
	}
	private where(tablename: string, fieldname: string, filter: any) {
		check(filter);
		const data = this._criteria[`${tablename}.${fieldname}`];
		if (!data) {
			this._criteria[`${tablename}.${fieldname}`] = filter;
		} else {
			this._criteria[`${tablename}.${fieldname}`] = Object.assign(filter, data);
		}
		return this;
	}
	private join(type: 'inner' | 'leftouter' | 'rightouter', table1: string, table2: string, ...fields: Array<[string, string]>) {
		this._relations.push([table1, table2, type, fields]);
		return this;
	}
	private init() {
		this._fields = [];
		this._relations = [];
		this._criteria = {};
		this._groupby = [];
		this._orderby = [];
		this.page_size = 0;
		this.page_no = 1;
	}
}

export default function complex_query(action_id: string, session_id: string, systemid: string) {
	return new Service(action_id, session_id, systemid);
}
